/**
 * excel操作类
 *
 * Created by snowingsea on 2020/11/17.
 */
const ExcelJS = require('exceljs');

class Excel {
  constructor(username = 'rocket') {
    this.workbook = new ExcelJS.Workbook();
    this.workbook.creator = username;
    this.workbook.lastModifiedBy = username;
    this.workbook.created = new Date();
    this.workbook.modified = new Date();

    this.lastSheet = null;
  }

  /**
   * 添加工作表
   * @param {String} sheetName 工作表名
   * @return {Object}
   */
  addSheet(sheetName) {
    this.lastSheet = this.workbook.addWorksheet(sheetName);
    return this.lastSheet;
  }

  /**
   * 设置单元格
   * @param {String} position 位置
   * @param {String} value 值
   * @param {Object} font 字体
   * @param {Object} alignment 对齐方式
   * @param {Object} fill 样式
   * @param {Object} border 边框样式
   */
  setCell(
    position,
    {
      value = undefined,
      font = undefined,
      alignment = { vertical: 'middle', horizontal: 'center' },
      fill = undefined,
      border = undefined,
      dataValidation = undefined,
    } = {},
  ) {
    const cell = this.lastSheet.getCell(position);
    if (value !== undefined && value !== null) {
      cell.value = value;
    }
    if (border) {
      cell.border = border;
    }
    if (font) {
      cell.font = font;
    }
    if (alignment) {
      cell.alignment = alignment;
    }
    if (fill) {
      cell.fill = fill;
    }
    if (dataValidation) {
      cell.dataValidation = dataValidation;
    }
  }

  /**
   * 设置行
   * @param {Number} row 行号
   * @param {Number} height 高
   * @param {Object} font 字体
   */
  setRow(row, { height = null, font = null }) {
    if (height) {
      this.lastSheet.getRow(row).height = height;
    }
    if (font) {
      this.lastSheet.getRow(row).font = font;
    }
  }

  // 获取行
  getRow(position) {
    return this.lastSheet.getRow(position);
  }

  // 获得单元格
  getCell(position) {
    return this.lastSheet.getCell(position);
  }

  /**
   * 设置列
   * @param {String} column 列
   * @param {Number} width 宽
   */
  setColumn(column, { width = null }) {
    if (width) {
      this.lastSheet.getColumn(column).width = width;
    }
  }

  /**
   * 合并单元格
   * @param {String} startPosition 起始点
   * @param {String} endPosition 终止点
   */
  mergeCells(startPosition, endPosition) {
    this.lastSheet.mergeCells(`${startPosition}:${endPosition}`);
  }

  /**
   * 添加行
   * @param {Array} columns 列信息
   * @param {Array} data 数据
   */
  addRows(columns, data) {
    this.lastSheet.columns = columns;
    this.lastSheet.addRows(data);
  }

  /**
   * 写出excel表格文件
   * @param {String} outputPath 输出文件路径
   * @return {Promise}
   */
  writeFile(outputPath) {
    return this.workbook.xlsx.writeFile(outputPath);
  }

  /**
   * 添加图片到单元格
   * @param {object} sheet
   * @param {string} cellPosition 单元格位置
   * @param {buffer} imageBuffer 图片文件buffer
   * @param {string} extname 图片格式
   * @returns
   */
  addImageToCell(cellPosition, imageBuffer, extname) {
    const imageId = this.workbook.addImage({
      buffer: imageBuffer,
      extension: extname,
    });
    this.lastSheet.addImage(imageId, cellPosition);
    return this.lastSheet.getCell(cellPosition);
  }

  /**
  * 读取表格数据返回json
  * @param {string} inputPath 需要读取的文件路径
  * @param {number} wrokSheetNum sheet页
  * @returns
  */
  readFile(inputPath, wrokSheetNum = 1, includeSheetName = false) {
    return new Promise((resolve, reject) => {
      this.workbook.xlsx.readFile(inputPath)
        .then(() => {
          const readData = [];
          const worksheet = this.workbook.worksheets[wrokSheetNum - 1];
          if (!worksheet) {
            resolve(readData);
          }
          worksheet.eachRow((row, rowNumber) => {
            const obj = { rowNumber, columns: [] };
            row.eachCell((cell, colNumber) => {
              obj.columns.push({ colNumber, type: cell.type, value: cell.text });
            });
            readData.push(obj);
          });
          if (includeSheetName) {
            resolve({ sheetName: worksheet.name, data: readData });
          }
          resolve(readData);
        })
        .catch(reject);
    });
  }
}

module.exports = Excel;
